/*  
*
*/
package com.ninearrows.db
{
    import flash.data.*
//    import flash.data.SQLConnection;
//    import flash.data.SQLMode;
//    import flash.data.SQLStatement;
    import flash.events.*;
    import flash.errors.SQLError;
    import flash.filesystem.File;
    
    import mx.controls.Alert;
    import mx.logging.ILogger;
    import mx.logging.Log;
    
    /**
     * 
     * 
     * Class used to create a sample product database. The database includes
     * three tables: Product, SKU, and Product_SKU. Product includes summary
     * information while the SKU table includes details about different
     * configurations each Product is available in. The Product_SKU table
     * associates which SKUs belong to a Product.
     * 
     * Each Product is generated with a random textual title and description.
     * Each SKU is generated with a random boolean availability, numerical price,
     * and assigned a random color and size from a hard coded list.
     * The number of SKUs associated with each Product is randomized but every
     * Product will have at least one SKU associated with it.
     * 
     * The class exposes one static method which takes in a file which will
     * be used to store the randomly generated catalog in. The code deletes
     * the given file and generates a new database file. The code does not provide
     * any status information while the database is being generated. Lastly
     * there is no error checking and any errors generated during database
     * creation will be thrown as SQLErrors.
     */
    public class DatabaseGenerator
    {
        private static var _myLogger:ILogger = Log.getLogger("com.database.DatabaseGenerator");
        
        private var _sqlConnection:SQLConnection;
        private var sqlStatement:SQLStatement = new SQLStatement();

    	private var nowDate:Date = new Date();;
    	private var nowStr:String = nowDate.getFullYear() + "-" + sprintf(nowDate.month) + "-" + sprintf(nowDate.date);

        private static const TARGET_TABLES:Array =
            ["dat_projects", "dat_projectcompsS", "dat_projectlogs", "dat_projectusers", "mst_users"];


        private const CREATE_PROJECTS:String =
			"CREATE TABLE dat_projects ( " +
			"local_id INTEGER PRIMARY KEY, " + 
			"id INTEGER, " + 
			"project_cd TEXT, project_name TEXT, " + 
			"start_date TEXT, " + 
			"delivery_date TEXT, " + 
			"valid_flg INTEGER, " + 
			"create_user_id INTEGER, " + 
			"created_on TEXT'" + nowStr + "', " + 
			"update_user_id INTEGER,  " + 
			"updated_on TEXT '" + nowStr + "', " + 
			"end_user_name TEXT,  " + 
			"local_edit_flg INTEGER  " + 
			");";

        private const DROP_PROJECT_COMPS:String =
			"DROP TABLE dat_projectcomps;";

        private const CREATE_PROJECT_COMPS:String =
			"CREATE TABLE dat_projectcomps " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"id INTEGER, " + 
			"project_id INTEGER, " + 
			"line_no INTEGER, " + 
			"task_kbn INTEGER, " + 
			"item_name TEXT, " + 
			"class_word1 TEXT, " + 
			"class_word2 TEXT, " + 
			"class_word3 TEXT, " + 
			"create_user_id INTEGER, " + 
			"created_on TEXT DEFAULT '" + nowStr + "', " + 
			"update_user_id INTEGER, " + 
			"updated_on TEXT DEFAULT '" + nowStr + "', " + 
			"last_operation_kbn INTEGER, " + 
			"local_edit_flg INTEGER  " + 
			");";


        private const DROP_PROJECT_LOGS:String =
			"DROP TABLE dat_projectlogs ;";

        private const CREATE_PROJECT_LOGS:String =
			"CREATE TABLE dat_projectlogs " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"id INTEGER, " + 
			"projectcomp_id INTEGER, " + 
			"log_kbn INTEGER, " + 
			"create_user_id INTEGER, " + 
			"created_on TEXT DEFAULT '" + nowStr + "', " + 
			"local_edit_flg INTEGER  " + 
			");";

        private const DROP_PROJECT_USERS:String =
			"DROP TABLE dat_projectusers;";

        private const CREATE_PROJECT_USERS:String =
			"CREATE TABLE dat_projectusers " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"id INTEGER, " + 
			"project_id INTEGER, " + 
			"email TEXT, " + 
			"user_id INTEGER, " + 
			"create_user_id INTEGER, " + 
			"created_on TEXT DEFAULT '" + nowStr + "', " + 
			"active_flg INTEGER DEFAULT 1, " + 
			"local_edit_flg INTEGER  " + 
			");";

        private const DROP_MST_USERS:String =
			"DROP TABLE mst_users;";

        private const CREATE_MST_USERS:String =
			"CREATE TABLE mst_users " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"id INTEGER, " + 
			"login_id TEXT, " + 
			"password TEXT, " + 
			"user_name TEXT, " + 
			"email TEXT, " + 
			"name TEXT, " + 
			"sex INTEGER, " + 
			"birthday TEXT, " + 
			"company_name TEXT, " + 
			"section_name TEXT, " + 
			"zip TEXT, " + 
			"prefecture TEXT, " + 
			"address1 TEXT, " + 
			"address2 TEXT, " + 
			"tel TEXT, " + 
			"fax TEXT, " + 
			"start_date TEXT, " + 
			"expire_date TEXT, " + 
			"valid_flg INTEGER DEFAULT 1, " + 
			"create_user_id INTEGER, " + 
			"created_on TEXT DEFAULT '" + nowStr + "', " + 
			"update_user_id INTEGER, " + 
			"updated_on TEXT DEFAULT '" + nowStr + "', " + 
			"srcpassword TEXT, " + 
			"skype_id TEXT, " + 
			"last_login_on TEXT DEFAULT '" + nowStr + "', " + 
			"local_edit_flg INTEGER  " + 
			");";


        private const CREATE_DAT_TASKS:String =
			"CREATE TABLE dat_tasks " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"  id INTEGER, " + 
			"  project_tree_id INTEGER, " + 
			"  priority_kbn INTEGER, " + 
			"  plan_power INTEGER, " + 
			"  fcas_power INTEGER, " + 
			"  exp_power INTEGER, " + 
			"  tani_kbn INTEGER, " + 
			"  client_user_id INTEGER, " + 
			"  main_user_id INTEGER, " + 
			"  complete_date TEXT, " + 
			"  progress_kbn INTEGER, " + 
			"  progress_rate INTEGER, " + 
			"  memo TEXT, " + 
			"  create_user_id INTEGER, " + 
			"  created_on TEXT, " + 
			"  update_user_id INTEGER, " + 
			"  updated_on TEXT, " + 
			"  task_cd TEXT, " + 
			"  start_date TEXT, " + 
			"  end_date TEXT, " + 
			"  report_date TEXT, " + 
			"  result TEXT, " + 
			"  last_operation_kbn INTEGER, " + 
			"  local_edit_flg INTEGER  " + 
			") ;";



        private const CREATE_DAT_TASKUSERS:String =
			"CREATE TABLE dat_taskusers " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"  id INTEGER, " + 
			"  task_id INTEGER, " + 
			"  projectuser_id INTEGER, " + 
			"  create_user_id INTEGER, " + 
			"  created_on TEXT, " + 
			"  local_edit_flg INTEGER  " + 
			") ;";


        private const CREATE_DAT_MILESTONES:String =
			"CREATE TABLE dat_milestones " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"  id INTEGER, " + 
			"  project_tree_id INTEGER, " + 
			"  mils_date TEXT, " + 
			"  create_user_id INTEGER , " + 
			"  created_on TEXT, " + 
			"  update_user_id INTEGER, " + 
			"  updated_on TEXT, " + 
			"  last_operation_kbn INTEGER, " + 
			"  local_edit_flg INTEGER  " + 
			") ;";


        private const CREATE_DAT_EVENTS:String =
			"CREATE TABLE dat_events " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"  id INTEGER, " + 
			"  project_tree_id INTEGER, " + 
			"  start_date TEXT, " + 
			"  start_time TEXT, " + 
			"  end_date TEXT, " + 
			"  end_time  TEXT, " + 
			"  allday_kbn  TEXT, " + 
			"  place  TEXT, " + 
			"  content  TEXT, " + 
			"  create_user_id INTEGER , " + 
			"  created_on TEXT, " + 
			"  update_user_id INTEGER, " + 
			"  updated_on TEXT, " + 
			"  last_operation_kbn INTEGER, " + 
			"  local_edit_flg INTEGER  " + 
			") ;";

        private const CREATE_DAT_EVENTUSERS:String =
			"CREATE TABLE dat_eventusers " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"  id INTEGER, " + 
			"  event_id INTEGER, " + 
			"  projectuser_id INTEGER, " + 
			"  entry_flg INTEGER, " + 
			"  create_user_id INTEGER, " + 
			"  created_on TEXT, " + 
			"  local_edit_flg INTEGER  " + 
			") ;";


        private const CREATE_MST_MESSAGES:String =
			"CREATE TABLE mst_messages " + 
			"( " + 
			"local_id INTEGER PRIMARY KEY, " + 
			"  id INTEGER, " + 
			"  msg_code TEXT, " + 
			"  msg_kbn INTEGER, " + 
			"  msg_base TEXT, " + 
			"  msg_jp TEXT, " + 
			"  msg_cn TEXT, " + 
			"  msg_en TEXT, " + 
			"  valid_flg INTEGER, " + 
			"  create_user_id INTEGER, " + 
			"  created_on TEXT, " + 
			"  update_user_id INTEGER, " + 
			"  updated_on TEXT, " + 
			"  local_edit_flg INTEGER  " + 
			") ;";


        // Data insertion statements.
        private static const INSERT_PROJECTS:String =
            "INSERT INTO dat_projects " + 
            "(id, project_cd, project_name, start_date, delivery_date, valid_flg, create_user_id, created_on, update_user_id, updated_on, end_user_name) " + 
            "VALUES " + 
            "(:id, :project_cd, :project_name, :start_date, :delivery_date, :valid_flg, :create_user_id, :created_on, :update_user_id, :updated_on, :end_user_name);";

        // Possible values for color and size SKU attributes.
        private static const COLORS:Array =
            ["red", "green", "blue", "black", "white", "orange", "yellow", "purple", "navy", "pearl", "brown", "tan"];
        private static const SIZES:Array =
            ["XS", "S", "M", "L", "XL"];
        
        // Source for random string generation.
        private static const RANDOM_TEXT:String =
            "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut " + 
            "labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris " + 
            "nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit " + 
            "esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt " + 
            "in culpa qui officia deserunt mollit anim id est laborum.";
        
        // SQL objects needed to create the database.
        private static var _sqlConnection:SQLConnection;
        private static var _insertProject:SQLStatement;        


        /**
         * Create database.
         * @param databaseFile File to create the database in.
         * @param productCount The number of products to include in the database (minimum of 1).
         */
        public function createDatabase(databaseFile:File, projectCount:int):Boolean
        {
            if ((databaseFile != null) && (databaseFile.exists))
            {
                
                databaseFile.deleteFile();
            }
            if (projectCount < 1)
            {
                projectCount = 1;
            }
            // Synchronous connection is used to simplify the coding
            // at the cost of not being able to provide status information
            // to the user about how many products have been inserted along
            // the way.
            _sqlConnection = new SQLConnection();
            _sqlConnection.open(databaseFile, SQLMode.CREATE);
            //var opened = _sqlConnection.open(databaseFile, SQLMode.CREATE);
            createSchema(databaseFile);
            _sqlConnection.close();
            
            return true;
        }
        
        
        /**
         * Create the schema in the database. 
         */
        private  function createSchema(databaseFile:File):void
        {
            for each (var statement:String in [CREATE_MST_USERS, CREATE_PROJECTS, CREATE_PROJECT_COMPS, CREATE_PROJECT_LOGS, CREATE_PROJECT_USERS, CREATE_DAT_TASKS, CREATE_DAT_TASKUSERS, CREATE_DAT_MILESTONES, CREATE_DAT_EVENTS, CREATE_DAT_EVENTUSERS, CREATE_MST_MESSAGES])
            {
                sqlStatement.sqlConnection = _sqlConnection;
                sqlStatement.text = statement;

			    sqlStatement.addEventListener(SQLEvent.RESULT, createResultHandler);
			    sqlStatement.addEventListener(SQLErrorEvent.ERROR, createErrorHandler);
				sqlStatement.execute();
 
            }
        }

		function createResultHandler(event:SQLEvent):void
		{
		    var result:SQLResult = sqlStatement.getResult();
		}

		function createErrorHandler(event:SQLErrorEvent):void
		{
			var eobj = event.error;
		    trace("An error occured while executing the statement.");
		}

        
        /**
         * Create the insert statements that will be used by the other
         * utility functions. Each insert statement uses parameters
         * allowing the statement to be prepared once and then just
         * have the values changed been executions.
         */
        private static function createInsertStatements():void
        {
            _insertProject = new SQLStatement();
            _insertProject.sqlConnection = _sqlConnection;
            _insertProject.text = INSERT_PROJECTS;
        }
        
        /**
         * Create all of the products in the database. 
         * @param productCount Number of products to create.
         */
        private static function createProjects(projectCount:int):void
        {
            // Insert all data in a single transaction for
            // improved performance.
            _sqlConnection.begin();
            for (var i:int = 1; i <= projectCount; i++)
            {
                createProject(i);
            } 
            _sqlConnection.commit();   
        }
        
        
        /**
         * Create an individual product in the database. 
         * @param productID ID of the product to create.
         */
        private static function createProject(projectID:int):void
        {
            // Bundle the insert statement parameters into
            // an Object. The ":" prefix is added in the
            // execute() utility method. 
            var data:Object = new Object();
            data.project_id = projectID;
            data.item_name = randomString(16);
            data.description = randomString(128);
            
            execute(_insertProject, data);
            
        }
        
        /**
         * Execute a SQLStatement against the database. Each property name in parameters
         * will have ":" prefixed to it before being set on the parameters object
         * associated with the sqlStatement.
         * @param sqlStatement SQLStatement to execute.
         * @param parameters Parameters to set on the SQLStatement before execution.
         */
        private static function execute(sqlStatement:SQLStatement, parameters:Object):void
        {
            sqlStatement.clearParameters();
            for (var name:String in parameters)
            {
                sqlStatement.parameters[":" + name] = parameters[name];
            }        
            sqlStatement.execute();
        }
        
        /**
         * Generate a random string that looks kind of real.
         * @param maxLength Maximum length of the string to generate (minimum of 4).
         * @return Randomly generated string.
         */
        private static function randomString(maxLength:int):String
        {
            if (maxLength < 4)
            {
                maxLength = 4;
            }
            var length:int = Math.min(int(Math.random() * maxLength) + 4, maxLength);
            // Extract a string of the specified length at some random starting
            // location within the source string.
            var startIndex:int = Math.random() * (RANDOM_TEXT.length - length);
            return RANDOM_TEXT.substr(startIndex, length);
        }

		private static function sprintf(num:int):String
		{
			if (num<10)
			{
				return "0"+String(num);
			}
			else
			{
				return String(num);
			}
		}
    }

}
